hi

I have an access 2010 database that was upscaled a while ago to use Sql Server 2008 as a data store.  My users have db_datareader and db_datawriter permissions which is enough for them.

One of the users would like to create new queries so I have given her create view and create function permissions on the database but she receives the error message "ADO error: The specified schema name "DOMAIN\Username" either does not exist or you do not have permission to use it" whenever she tries to save a query.  It looks like Access is trying to create the view in the schema domain\username instead of dbo.  If I give the user db_owner permissions the view is saved correctly in the dbo schema.

Is there a way to tell access to use the dbo schema by default without giving the user db_owner permissions?

Thanks

Hi,

Based on my test, if you make the user the owner of the database (rather than a user with the db_owner role), when you create a query, it creates it under the dbo schema rather than DOMAIN\username.

Steps to do so (in Management Studio):

Right click database, select Properties 
Click File 
Change Owner in the textbox 
OK to confirm 
Downside - other users under db_owner role will still have their username appended. So schemas have to be created for these users.